1. Field of the Invention
This invention relates in general to relational database management systems, and, in particular, to a technique for detecting a subsuming temporal relationship of valid time data in a relational database management system.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on random access storage devices (RASD) such as magnetic or optical disk drives for semi-permanent storage.
RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO). The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages, such as C and COBOL. SQL allows the user to manipulate the data.
A data warehouse is a combination of many different databases across an entire enterprise. Data warehouses contain a wide variety of data that presents a coherent picture of business conditions at a single point in time. As a result, many companies use data warehouses to support management decision making. A data mart is similar to a data warehouse. The only difference between the data mart and the data warehouse is that data marts are usually smaller than data warehouses, and data marts focus on a particular subject or departments. Both the data warehouse and the data mart use the RDBMS for storing and retrieving information.
Companies frequently use data warehouses and data marts to create billions of bytes of data about all aspects of a company, including facts about their customers, products, operations, and personal. Many companies use this data to evaluate their past performance and to plan for the future. To assist the companies in analyzing this data, some data warehousing and decision support professionals write applications and generate reports that seek to shed light on a company""s recent business history.
Several common forms of data analysis involve evaluating time-related data, such as examining customer buying behaviors, assessing the effectiveness of marketing campaigns or determining the impact of organizational changes on sales during a selected time period. The relevance of time-related data to a variety of business applications has caused some DBMS professionals to reexamine the need for temporal data analysis.
Temporal data is often used to track the period of time at which certain business conditions are valid. To illustrate, a company may sell product X for: $50 during a first period of time; $45 during a second period of time; and $52 during a third period of time. The company may even know that this same product will sell for $54 during some future period of time. When the company""s database contains information about the valid times for each of these price points, the pricing points are referred to as temporal data. Common techniques for recording valid time information in a RDBMS involve including a DATE column in a table that tracks business conditions, such as a START_DATE and an END_DATE column in a table that tracks pricing information for products.
Analyzing temporal data involves understanding the manner in which different business conditions relate to one another over time. Returning to the previous example, each product has a retail price for a given period of time, and each product also has a wholesale cost. Retail prices can fluctuate independently of the product""s wholesale cost, and vice versa. To determine efficiencies (or inefficiencies) in a product""s pricing scheme, a retailer may wish to understand the relationship between a product""s retail price and a product""s wholesale cost over time. More specifically, a retailer may wish to evaluate: whether products are being placed on sale at inopportune times (e.g., before the retailer is eligible to receive a reduction in wholesale price) or whether the retailer has failed to pass on cost savings to customers (e.g., failing to place products on sale during the period in which their wholesale cost is reduced).
These questions involve temporal analysis because the questions involve tracking the period of time at which certain business conditions were in effect. These questions can be challenging to express in SQL, and many users are incapable of correctly formatting such SQL queries. Further, mistakes in the SQL query are common and difficult to detect.
Thus, there is a need in the art for a technique of creating a simplified SQL query to analyze the temporal relationships of various business conditions.
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for detecting subsuming temporal relationships in a relational database.
In accordance with the present invention, an invocation of a within operation that specifies a first event and a second event is received. In response to the invocation, a combination of temporal relationships between the first event and the second event is evaluated to determine (1) whether the second event starts at the same time as the first event or whether the second event starts before the first event and (2) whether the second event ends at the same time as the first event or whether the second event ends after the first event.